package org.outlook.apps.goal.db;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.outlook.apps.goal.pojo.Column;
import org.outlook.apps.goal.pojo.Goal;

public class SQLiteDBSource implements DBSource {
	
	private static ThreadLocal<Connection> connPool = new ThreadLocal<Connection>();
	private static Connection conn;

	static {
		conn = openConnection();
		if(conn != null)
			connPool.set(conn);
	}

	public static Connection getConnection() {
		//TODO
		return conn;
	}

	public static Connection openConnection() {
		try {
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection("jdbc:sqlite:goal.db");
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	private void closeConnection(Connection conn, Statement stat, ResultSet rs) {
		try {
			if(rs != null)
				rs.close();
			if(stat != null)
				stat.close();
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			rs = null;
			stat = null;
			conn = null;
		}
	}
	
	public static void openTransaction() {
		try {
			Connection conn = getConnection();
			conn.setAutoCommit(false);
			conn.setTransactionIsolation(2);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void roolback() {
		try {
			Connection conn = getConnection();
			if (conn != null)
				conn.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	} 
	
	public boolean execute(String sql, Object...objs) {
		Connection conn = getConnection();
		PreparedStatement stat = null;
		try {
			stat = conn.prepareStatement(sql);
			int index = 0;
			for(Object obj:objs)
				stat.setString(++index, obj.toString());
			return stat.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, stat, null);
		}
		return false;
	}

	@Override
	public List<Map<String, String>> query(String sql, Object[] objs) {
		Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(sql);
			int index = 0;
			if(objs==null)
				objs = new Object[]{};
			for(Object obj:objs)
				stat.setString(++index, obj.toString());
			rs = stat.executeQuery();

			List<Map<String, String>> list = new ArrayList<Map<String, String>>();
			Map<String, String> row;
			ResultSetMetaData rsmd = rs.getMetaData();
	        int cols = rsmd.getColumnCount();
			while(rs.next()) {
				row = new HashMap<String, String>(cols);
				for(index=1; index<=cols; index++) {
					String field = rsmd.getColumnName(index);
                    String value = rs.getString(index);
                    row.put(field, value);
				}

				list.add(row);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, stat, rs);
		}
		return null;
	}

	@Override
	public List<Map<?, Object>> query(String sql, Object[] objs, Column[] columns) {
		Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(sql);
			int index = 0;
			for(Object obj:objs)
				stat.setString(++index, obj.toString());
			rs = stat.executeQuery();

			List<Map<?, Object>> list = new ArrayList<Map<?, Object>>();
			Map<Object, Object> row;
			ResultSetMetaData rsmd = rs.getMetaData();
	        int cols = rsmd.getColumnCount();
			while(rs.next()) {
				row = new HashMap<Object, Object>(cols);
				for(index=1; index<=cols; index++) {
					String field = rsmd.getColumnName(index);
					Column column = getColumnByColumnName(columns, field);
                    Object value = getValue(rs, column);
                    row.put(field, value);
				}

				list.add(row);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, stat, rs);
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public <T extends Object> List<T> getEntityList(T entity, String sql, Object... obj) {
		List<T> list = new ArrayList<T>();
		Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(sql);
			stat = formatSql(stat, obj);
			rs = stat.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			String[] columnsName = getColumnName(rsmd);
			Class clazz = entity.getClass();
			Object entityObject = null;
			Field[] fields = clazz.getDeclaredFields();
			while (rs.next()) {
				entityObject = clazz.newInstance();
				for (int i = 0; i < columnsName.length; i++) {
					for (int k = 0; k < fields.length; k++) {
						if (isMapping(columnsName[i], fields[k])) {
							boolean accessible = fields[k].isAccessible();
							fields[k].setAccessible(true);
							fields[k].set(entityObject, rs.getString(columnsName[i]));
							fields[k].setAccessible(accessible);
						}
					}
				}
				list.add((T) entityObject);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, stat, rs);
		}
		return list;
	}

	private boolean isMapping(String columnsName, Field field) {
		String fieldName = field.getName();
		if(fieldName.equalsIgnoreCase(columnsName))
			return true;
		else if(fieldName.replace("_", "").equalsIgnoreCase(columnsName))
			return true;
		else
			return false;
	}

	public String[] getColumnName(ResultSetMetaData rsmd) {
		String[] columnName = null;
		try {
			columnName = new String[rsmd.getColumnCount()];
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				columnName[i] = rsmd.getColumnName(i + 1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return columnName;
	} 
	
	private Column getColumnByColumnName(Column[] columns, String field) {
		for(Column column : columns)
			if(column.getName().equals(field))
				return column;
		return null;
	}

	@Override
	@SuppressWarnings("unchecked")
	public Collection<?> queryAll(String sql, Object[] objs, Column[] columns) {
		Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(sql);
			int index = 0;
			for(Object obj:objs)
				stat.setString(++index, obj.toString());
			rs = stat.executeQuery();
			List list = new ArrayList();
			Object[] row;
			while(rs.next()) {
				row = new Object[columns.length];
				for(Column column:columns) {
					row[index++] = getValue(rs, column);
				}
				list.add(row);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, stat, rs);
		}
		return null;
	}

	private Object getValue(ResultSet rs, Column column) {
		try {
			if(column.getClazz() == String.class) {
				if(column.getName() == null)
					return rs.getString(column.getIndex());
				else
					return rs.getString(column.getName());
			} else if(column.getClazz() == Long.class) {
				if(column.getName() == null)
					return rs.getLong(column.getIndex());
				else
					return rs.getLong(column.getName());
			} else if(column.getClazz() == Integer.class) {
				if(column.getName() == null)
					return rs.getInt(column.getIndex());
				else
					return rs.getInt(column.getName());
			} else if(column.getClazz() == Date.class) {
				if(column.getName() == null)
					return rs.getTimestamp(column.getIndex());
				else
					return rs.getTimestamp(column.getName());
			} else {
				if(column.getName() == null)
					return rs.getObject(column.getIndex());
				else
					return rs.getObject(column.getName());
			}
		} catch (Exception e) {
		}
		return null;
	}

	public Collection<Goal> getGoalList() {
		Connection conn = getConnection();
		Statement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery("select * from goals");
			List<Goal> list = new ArrayList<Goal>();
			while(rs.next()) {
				list.add(new Goal(rs.getLong("id"), rs.getString("title"),	rs.getString("description")));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, stat, rs);
		}
		return null;
	}

	@Override
	public int queryCount(String sql, Object[] objects) {
		Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(sql);
			int index = 0;
			for(Object obj:objects)
				stat.setString(++index, obj.toString());
			rs = stat.executeQuery();
			int count=0;
			if(rs.next())
				count++;
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, stat, rs);
		}
		return 0;
	}
	
	public PreparedStatement formatSql(PreparedStatement pStatement, Object... obj) {
		try {
			if (obj.length != 0) {
				for (int i = 1; i <= obj.length; i++) {
					pStatement.setObject(1, obj[i]);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pStatement;
	}

}
